package ch.poole.openinghoursfragment.templates;

import ohos.data.rdb.RawRdbPredicates;
import ohos.data.rdb.RdbStore;
import ohos.data.rdb.ValuesBucket;
import ohos.data.resultset.ResultSet;
import org.jetbrains.annotations.Nullable;
import org.json.JSONArray;

import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.stream.Collectors;


/**
 * Access methods for the template database
 *
 * @author simon
 */
public final class TemplateDatabase {
    private static final String DEBUG_TAG = "TemplateDatabase";

    private static final String TEMPLATES_TABLE = "templates";
    static final String TEMPLATE_FIELD = "template";
    static final String DEFAULT_FIELD = "is_default";
    static final String NAME_FIELD = "name";
    static final String KEY_FIELD = "key";
    static final String REGION_FIELD = "region";
    static final String OBJECT_FIELD = "object";

    static final String QUERY_ALL = "SELECT rowid as _id, key, name, is_default, template, region, object FROM templates";
    static final String QUERY_BY_ROWID = "SELECT key, name, is_default, template, region, object FROM templates WHERE rowid=?";
    static final String QUERY_BY = "SELECT rowid as _id, key, name, is_default, template, region, object FROM templates WHERE ";
    static final String QUERY_TEMPLATE_BY = "SELECT template FROM templates WHERE ";


    /**
     * Return the default template entry if any
     *
     * @param database readable template database
     * @param key      key we are looking for the default for
     * @param region   the region or null
     * @param object   the object or null
     * @return the default template or null if non
     */
    @Nullable
    public static String getDefault(RdbStore database, @Nullable String key, @Nullable String region, @Nullable String object) {
        String result = null;
        List<String> params = new ArrayList<>();
        StringBuilder query = new StringBuilder();
        if (key != null) {
            query.append("(key is NULL OR key=?)");
            params.add(key);
        }
        if (region != null) {
            if (query.length() > 0) {
                query.append(" AND ");
            }
            query.append("(region is NULL OR region=?)");
            params.add(region);
        }

        if (object != null) {
            if (query.length() > 0) {
                query.append(" AND ");
            }
            query.append("(object IS NULL OR object LIKE ?)");
            params.add(object);
        }
        if (query.length() > 0) {
            query.append(" AND ");
        }
        query.append("is_default=1");
        ResultSet dbresult = database.querySql(TemplateDatabase.QUERY_TEMPLATE_BY + query.toString(), params.toArray(new String[0]));
        dbresult.goToFirstRow();
        if (dbresult.getRowCount() >= 1) {
            result = dbresult.getString(0);
        }
        dbresult.close();
        return result;
    }

    /**
     * Get all entries for a specific key plus all which apply to any key
     *
     * @param database the database to query
     * @param key      the key or null if we want entries for all keys
     * @param region   the region or null if we want all entries for all regions
     * @param object   the object or null if we want all entries for all objects
     * @return a Cursor pointing to the first entry
     */
    public static ResultSet queryBy(RdbStore database, @Nullable String key, @Nullable String region, @Nullable String object) {
        if (key == null && region == null && object == null) {
            return database.querySql(TemplateDatabase.QUERY_ALL, null);
        } else {
            List<String> params = new ArrayList<>();
            StringBuilder query = new StringBuilder();
            List<String> orderCols = new ArrayList<>();
            if (key != null) {
                query.append("(key is NULL OR key=?)");
                params.add(key);
                orderCols.add("key");
            }
            if (region != null) {
                if (query.length() > 0) {
                    query.append(" AND ");
                }
                String[] regionParts = region.split("-");
                if (regionParts.length > 1) { // this will add a check for the country
                    query.append("(region is NULL OR region=? OR region=?)");
                    params.add(region);
                    params.add(regionParts[0]);
                } else {
                    query.append("(region is NULL OR region=?)");
                    params.add(region);
                }
                orderCols.add(REGION_FIELD);
            }
            if (object != null) {
                if (query.length() > 0) {
                    query.append(" AND ");
                }
                query.append("(object IS NULL OR object LIKE ?)");
                params.add(object);
                orderCols.add(OBJECT_FIELD);
            }
            query.append(" ORDER BY ");
            for (String col : orderCols) {
                query.append("LENGTH(");
                query.append(col);
                query.append(") DESC, ");
            }
            query.append("is_default DESC");
            return database.querySql(TemplateDatabase.QUERY_BY + query.toString(), params.toArray(new String[0]));
        }
    }

    /**
     * Add a new template with the given values to the database
     *
     * @param db          writable template database
     * @param key         key the template should be used for, or null if applicable for all
     * @param name        name of the template
     * @param defaultFlag true if default
     * @param template    template itself
     * @param region      region this entry applies to or null
     * @param object      object this entry applies to (typically a list of OSM tags) or null
     */
    public static void add(RdbStore db, @Nullable String key, String name, boolean defaultFlag, String template,
                           @Nullable String region, @Nullable String object) {
        if (defaultFlag) { // set all existing is_default values to false
            resetDefaultFlags(db, key);
        }
        ValuesBucket values = new ValuesBucket();
        values.putString(KEY_FIELD, key);
        values.putString(NAME_FIELD, name);
        values.putInteger(DEFAULT_FIELD, defaultFlag ? 1 : 0);
        values.putString(TEMPLATE_FIELD, template);
        values.putString(REGION_FIELD, region);
        values.putString(OBJECT_FIELD, object);
        long insert = db.insert(TEMPLATES_TABLE, values);
        Logger.getLogger("TemplateDialog").log(Level.WARNING, "添加成功没:" + insert);
    }

    /**
     * Update an existing template entry
     *
     * @param db          writable template database
     * @param id          rowid of the template
     * @param key         key the template should be used for, or null if applicable for all
     * @param name        name of the template
     * @param defaultFlag true if default
     * @param template    template itself
     * @param region      region this entry applies to or null
     * @param object      object this entry applies to (typically a list of OSM tags) or null
     */
    static void update(RdbStore db, int id, @Nullable String key, String name, boolean defaultFlag, String template,
                       @Nullable String region, @Nullable String object) {
        if (defaultFlag) { // set all existing is_default values to false
            resetDefaultFlags(db, key);
        }
        ValuesBucket values = new ValuesBucket();
        values.putString(KEY_FIELD, key);
        values.putString(NAME_FIELD, name);
        values.putInteger(DEFAULT_FIELD, defaultFlag ? 1 : 0);
        values.putString(TEMPLATE_FIELD, template);
        values.putString(REGION_FIELD, region);
        values.putString(OBJECT_FIELD, object);


        RawRdbPredicates rdbPredicates = new RawRdbPredicates(TEMPLATES_TABLE);
        rdbPredicates.setWhereClause("rowid=" + id);
        rdbPredicates.setWhereArgs(null);
        db.update(values, rdbPredicates);
    }

    /**
     * Delete an entry in the template database
     *
     * @param db writable template database
     * @param id rowid of the template
     */
    static void delete(final RdbStore db, final int id) {
        RawRdbPredicates rdbPredicates = new RawRdbPredicates(TEMPLATES_TABLE);
        rdbPredicates.setWhereClause("rowid=?");
        rdbPredicates.setWhereArgs(Arrays.asList(new String[]{Integer.toString(id)}.clone()));
        db.delete(rdbPredicates);
    }

    /**
     * Set the default flag to false for all entries for a specific key
     *
     * @param key key the template should be used for, or null if applicable for all
     * @param db  writable template database
     */
    private static void resetDefaultFlags(RdbStore db, @Nullable String key) {
        ValuesBucket values = new ValuesBucket();
        values.putInteger(DEFAULT_FIELD, 0);
        RawRdbPredicates rdbPredicates = new RawRdbPredicates(TEMPLATES_TABLE);
        rdbPredicates.setWhereClause("key = '" + key + "'");
        rdbPredicates.setWhereArgs(null);
        db.update(values, rdbPredicates);
    }

    /**
     * Write the contents of the database to an OutputStream
     *
     * @param database a readable instance of the database
     * @param out      the OutputStream
     */
    static void writeJSON(RdbStore database, OutputStream out) {
        ResultSet cursor = queryBy(database, null, null, null);
        int rows = cursor.getRowCount();
        try (JsonWriter writer = new JsonWriter(new PrintWriter(out))) {
            writer.beginArray();
            if (rows > 0) {
                cursor.goToFirstRow();
                rowToJson(writer, cursor);
                while (cursor.goToNextRow()) {
                    rowToJson(writer, cursor);
                }
            } else {
            }
            writer.endArray();
            writer.flush();
        } catch (IOException e) {
        }
    }

    /**
     * Write a database row to JSON
     *
     * @param writer a JsonWriter
     * @param cursor the database cursor
     * @throws IOException if writing goes wrong
     */
    private static void rowToJson(JsonWriter writer, ResultSet cursor) throws IOException {
        writer.beginObject();
        writer.name(NAME_FIELD);
        writer.value(cursor.getString(cursor.getColumnIndexForName(NAME_FIELD)));
        writer.name(DEFAULT_FIELD);
        writer.value(cursor.getInt(cursor.getColumnIndexForName(DEFAULT_FIELD)));
        writer.name(TEMPLATE_FIELD);
        writer.value(cursor.getString(cursor.getColumnIndexForName(TEMPLATE_FIELD)));
        writer.name(KEY_FIELD);
        writer.value(cursor.getString(cursor.getColumnIndexForName(KEY_FIELD)));
        writer.name(REGION_FIELD);
        writer.value(cursor.getString(cursor.getColumnIndexForName(REGION_FIELD)));
        writer.name(OBJECT_FIELD);
        writer.value(cursor.getString(cursor.getColumnIndexForName(OBJECT_FIELD)));
        writer.endObject();
    }

    /**
     * Load the contents of a Json InputStream in to the template database
     *
     * @param database the database instance
     * @param in       the InputStream
     * @param replace  if true existing content will be replaced
     * @return true if reading and adding was successful
     * @throws IOException IOException
     */
    static boolean loadJson(RdbStore database, InputStream in, boolean replace) {
        if (replace) {
            database.executeSql("delete from templates");
        }
        String result = new BufferedReader(new InputStreamReader(in))
                .lines().collect(Collectors.joining(System.lineSeparator()));
        try {

            JSONArray objects = new JSONArray(result);
            addRowFromJson(database, objects);
            return true;
        } catch (IOException | IllegalStateException e) {
            return false;
        } finally {
        }
    }

    /**
     * Read a row from JSON and add it to the database
     *
     * @param database   the database
     * @param jsonObject a jsonObject
     * @throws IOException if something goes wrong while reading
     */
    private static void addRowFromJson(RdbStore database, JSONArray jsonObject) throws IOException {
        for (int i = 0; i < jsonObject.length(); i++) {
            ValuesBucket values = new ValuesBucket();
            values.putString(KEY_FIELD, jsonObject.getJSONObject(i).get(KEY_FIELD).toString().equals("null") ? "" : jsonObject.getJSONObject(i).get(KEY_FIELD).toString());
            values.putString(NAME_FIELD, jsonObject.getJSONObject(i).get(NAME_FIELD).toString().equals("null") ? null : (String) jsonObject.getJSONObject(i).get(NAME_FIELD));
            values.putInteger(DEFAULT_FIELD, jsonObject.getJSONObject(i).get(DEFAULT_FIELD).toString().equals("null") ? 0 : (Integer) jsonObject.getJSONObject(i).get(DEFAULT_FIELD));
            values.putString(TEMPLATE_FIELD, jsonObject.getJSONObject(i).get(TEMPLATE_FIELD).toString().equals("null") ? null : (String) jsonObject.getJSONObject(i).get(TEMPLATE_FIELD));
            values.putString(REGION_FIELD, jsonObject.getJSONObject(i).get(REGION_FIELD).toString().equals("null") ? null : (String) jsonObject.getJSONObject(i).get(REGION_FIELD));
            values.putString(OBJECT_FIELD, jsonObject.getJSONObject(i).get(OBJECT_FIELD).toString().equals("null") ? null : (String) jsonObject.getJSONObject(i).get(OBJECT_FIELD));
            long insert = database.insert(TEMPLATES_TABLE, values);

        }
        ResultSet resultSet = TemplateDatabase.queryBy(database, null, null, null);
        int rowCount = resultSet.getRowCount();
    }
}
